﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Lists;
using BasicForms;


namespace ReportGenerators
{
    public partial class FrmThamSoBaoCaoKinhDoanhLaiLo : BasicForms.FrmThamSoBaoCao
    {
        public FrmThamSoBaoCaoKinhDoanhLaiLo()
        {
            InitializeComponent();
        }

        protected override void PrepareDataForReport()
        {
            BasicForms.FrmProgressBarDev.StartShowProgress();

            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            ReportTemplates.Khac.DsBaoCaoKinhDoanhLaiLo ds = new ReportTemplates.Khac.DsBaoCaoKinhDoanhLaiLo();
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                SqlDataAdapter da = null;

                cmd.CommandText = "Select_ThongTinDoanhNghiep2";
                cmd.Parameters.Clear();
                da = new SqlDataAdapter(cmd);
                da.Fill(ds.ThongTinDoanhNghiep);

                ds.ChiTietBaoCaoKinhDoanhLaiLo.Rows.Add(ds.ChiTietBaoCaoKinhDoanhLaiLo.NewRow());
                ds.ChiTietBaoCaoKinhDoanhLaiLo.Rows[0]["KyBaoCao"] = _KyBaoCao.Ten;

                DataTable dtMuaHang = new DataTable();
                DataTable dtHangMuaTraLai = new DataTable();
                DataTable dtBanHang = new DataTable();
                DataTable dtHangBanTraLai = new DataTable();

                #region GetData MuaHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma ='HoaDonMuaHang') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtMuaHang);
                #endregion
                #region GetData HangMuaTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangMuaTraLai') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangMuaTraLai);
                #endregion
                #region GetData BanHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, Sum(C.Value3) GiaTriXuat, SUM(C.Value8 * C.Value13)/100 TienChietKhau, SUM(C.Value5 - C.Value3) TienLai \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HoaDonBanLe' OR Ma ='HoaDonBanHang') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtBanHang);
                #endregion
                #region GetData HangBanTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, SUM(C.Value5 - C.Value3) TienGiamLai \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangBanTraLai') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangBanTraLai);
                #endregion

                DataTable dtHangHoaCoGiaoDich = new DataTable();
                if (cmbHang.EditValue is Guid && (Guid)cmbHang.EditValue != Guid.Empty)
                {
                    cmd.CommandText = string.Format("SELECT CAST('{0}' AS uniqueidentifier) VatTuHangHoaID", cmbHang.EditValue);
                }
                else if (cmbNhomHang.EditValue is Guid && (Guid)cmbNhomHang.EditValue != Guid.Empty)
                {
                    cmd.CommandText = string.Format("SELECT DISTINCT C.VatTuHangHoa1ID VatTuHangHoaID, D.Ten \n" +
                                                    "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                                    "               INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                                    "               INNER JOIN VatTuHangHoa D ON C.VatTuHangHoa1ID = D.ID \n" +
                                                    "WHERE  A.LoaiChungTuID IN (SELECT ID \n" +
                                                    "                           FROM LoaiChungTu \n" +
                                                    "                           WHERE Ma ='HoaDonMuaHang' OR Ma = 'HangMuaTraLai' \n" +
                                                    "                               OR Ma = 'HoaDonBanLe' OR Ma ='HoaDonBanHang' OR Ma = 'HangBanTraLai') \n" +
                                                    "       AND A.GhiSo = 1  \n" +
                                                    "       AND C.VatTuHangHoa1ID IS NOT NULL \n" +
                                                    "       AND D.NhomVatTuHangHoaID = '{0}' \n" +
                                                    "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n" +
                                                    "ORDER BY D.Ten\n"
                                                    , cmbNhomHang.EditValue);
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                    cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                }
                else
                {
                    cmd.CommandText = "SELECT DISTINCT C.VatTuHangHoa1ID VatTuHangHoaID, D.Ten \n" +
                                    "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID  \n" +
                                    "               INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                    "               INNER JOIN VatTuHangHoa D ON C.VatTuHangHoa1ID = D.ID \n" +
                                    "WHERE  A.LoaiChungTuID IN (SELECT ID \n" +
                                    "                           FROM LoaiChungTu \n" +
                                    "                           WHERE Ma ='HoaDonMuaHang' OR Ma = 'HangMuaTraLai' \n" +
                                    "                               OR Ma = 'HoaDonBanLe' OR Ma ='HoaDonBanHang' OR Ma = 'HangBanTraLai') \n" +
                                    "       AND A.GhiSo = 1  \n" +
                                    "       AND C.VatTuHangHoa1ID IS NOT NULL \n" +
                                    "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi \n" +
                                    "ORDER BY D.Ten\n";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                    cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                }
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangHoaCoGiaoDich);

                for (int i = 0; i < dtHangHoaCoGiaoDich.Rows.Count; i++)
                {
                    VatTuHangHoa hangHoa = DanhMucVatTuHangHoa.Instance.GetByID((Guid)dtHangHoaCoGiaoDich.Rows[i]["VatTuHangHoaID"]);
                    if (hangHoa != null)
                    {
                        BasicForms.FrmProgressBarDev.Status = string.Format("Đang tính {0}/{1}", i, dtHangHoaCoGiaoDich.Rows.Count);

                        DataRow row = ds.KhoanMucBaoCaoKinhDoanhLaiLo.NewRow();

                        row["TenNhomVatTuHangHoa"] = (hangHoa.NhomVatTuHangHoa != null) ? hangHoa.NhomVatTuHangHoa.Ten : "Chưa xác định";
                        row["MaVatTuHangHoa"] = hangHoa.Ma;
                        row["TenVatTuHangHoa"] = hangHoa.Ten;

                        TonKho tonDauKy = TonKho.CalculateTongTonTuThoiDiemKhoaSoGanNhatDenThoiDiemNhapVao(_KyBaoCao.ThoiDiemDau, hangHoa.ID);
                        row["SoLuongTonDauKy"] = tonDauKy.SoLuong;
                        row["GiaTriTonDauKy"] = tonDauKy.ThanhTien;

                        DataRow[] rowsMuaHang = dtMuaHang.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                        if (rowsMuaHang.Length == 1)
                        {
                            if (rowsMuaHang[0]["SoLuong"] is decimal && rowsMuaHang[0]["ThanhTien"] is decimal)
                            {
                                row["SoLuongMua"] = (decimal)rowsMuaHang[0]["SoLuong"];
                                row["GiaTriMua"] = (decimal)rowsMuaHang[0]["ThanhTien"];
                            }
                        }

                        DataRow[] rowsHangMuaTraLai = dtHangMuaTraLai.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                        if (rowsHangMuaTraLai.Length == 1)
                        {
                            if (rowsHangMuaTraLai[0]["SoLuong"] is decimal && rowsHangMuaTraLai[0]["ThanhTien"] is decimal)
                            {
                                row["SoLuongHangMuaTraLai"] = (decimal)rowsHangMuaTraLai[0]["SoLuong"];
                                row["GiaTriHangMuaTraLai"] = (decimal)rowsHangMuaTraLai[0]["ThanhTien"];
                            }
                        }

                        DataRow[] rowsBanHang = dtBanHang.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                        if (rowsBanHang.Length == 1)
                        {
                            if (rowsBanHang[0]["SoLuong"] is decimal && rowsBanHang[0]["ThanhTien"] is decimal)
                            {
                                row["SoLuongBan"] = (decimal)rowsBanHang[0]["SoLuong"];
                                row["GiaTriBan"] = (decimal)rowsBanHang[0]["ThanhTien"];
                            }
                            if (rowsBanHang[0]["TienChietKhau"] is decimal)
                            {
                                row["TongTienChietKhau"] = (decimal)rowsBanHang[0]["TienChietKhau"];
                            }
                            if (rowsBanHang[0]["TienLai"] is decimal)
                            {
                                row["LaiGop"] = (decimal)rowsBanHang[0]["TienLai"];
                            }
                            else
                            {
                            }
                            if (rowsBanHang[0]["GiaTriXuat"] is decimal)
                            {
                                row["GiaTriXuat"] = (decimal)rowsBanHang[0]["GiaTriXuat"];
                            }
                        }

                        DataRow[] rowsHangBanTraLai = dtHangBanTraLai.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                        if (rowsHangBanTraLai.Length == 1)
                        {
                            if (rowsHangBanTraLai[0]["SoLuong"] is decimal && rowsHangBanTraLai[0]["ThanhTien"] is decimal)
                            {
                                row["SoLuongHangBanTraLai"] = (decimal)rowsHangBanTraLai[0]["SoLuong"];
                                row["GiaTriHangBanTraLai"] = (decimal)rowsHangBanTraLai[0]["ThanhTien"];
                            }
                            //if (rowsHangBanTraLai[0]["TienGiamLai"] is decimal)
                            //{
                            //    row["LaiGop"] = (decimal)row["LaiGop"] - (decimal)rowsHangBanTraLai[0]["TienGiamLai"];
                            //}
                        }

                        row["SoLuongTonCuoi"] = tonDauKy.SoLuong + (decimal)row["SoLuongMua"] - (decimal)row["SoLuongHangMuaTraLai"]
                                                                    - (decimal)row["SoLuongBan"] + (decimal)row["SoLuongHangBanTraLai"];

                        if ((decimal)row["SoLuongTonCuoi"] > 0)
                            row["GiaTriTonCuoi"] = tonDauKy.ThanhTien + (decimal)row["GiaTriMua"] - (decimal)row["GiaTriHangMuaTraLai"]
                                                - (decimal)row["GiaTriXuat"] + (decimal)row["GiaTriHangBanTraLai"];
                        else
                            row["GiaTriTonCuoi"] =0;

                        if ((decimal)row["SoLuongBan"] != 0 || (decimal)row["SoLuongHangBanTraLai"] > 0)
                            ds.KhoanMucBaoCaoKinhDoanhLaiLo.Rows.Add(row);
                    }
                }

                if (ds.Tables.Count > 0)
                {
                    _Report = new ReportTemplates.Khac.RptBaoCaoKinhDoanhLaiLo();
                    _Report.SetDataSource(ds);
                }
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
            finally
            {
                con.Close();
                con = null;
            }

            BasicForms.FrmProgressBarDev.FinishShowProgress();
        }

        protected override void PrintReport()
        {
            if (_Report != null)
            {
                ReportTemplates.FrmPrintReport frm = new ReportTemplates.FrmPrintReport(_Report, "Báo cáo tính hình mua bán lãi lỗ");
                frm.Show();
            }
        }

        private void FrmThamSoBaoCaoKinhDoanhLaiLo_Load(object sender, EventArgs e)
        {
            if (DesignMode) return;

            #region cmbHang
            cmbHang.DataSource =DanhMucVatTuHangHoa.GetDanhMucHangHoaDangSuDung().DataView;
            cmbHang.DisplayColumn = "Ten";
            cmbHang.ValueColumn = "ID";
            cmbHang.AddVisibleColumn("Ma", "Mã");
            cmbHang.AddVisibleColumn("Ten", "Tên");
            cmbHang.EditValue = Guid.Empty;
            #endregion

            #region cmbNhomHang
            cmbNhomHang.DataSource = DanhMucNhomVatTuHangHoa.Instance.DataView;
            cmbNhomHang.DisplayColumn = "Ten";
            cmbNhomHang.ValueColumn = "ID";
            cmbNhomHang.AddVisibleColumn("Ma", "Mã");
            cmbNhomHang.AddVisibleColumn("Ten", "Tên");
            cmbNhomHang.EditValue = Guid.Empty;
            #endregion
        }

        private void cmbHang_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                if ((cmbNhomHang.EditValue is Guid && (Guid)cmbNhomHang.EditValue != Guid.Empty) && (cmbHang.EditValue is Guid && (Guid)cmbHang.EditValue != Guid.Empty))
                {
                    string filterExpresstion =  string.Format("ID = '{0}' AND NhomVatTuHangHoaID = '{1}'", cmbHang.EditValue, cmbNhomHang.EditValue);
                    DanhMucVatTuHangHoa dmVTHH = DanhMucVatTuHangHoa.Filter(filterExpresstion);
                    if (dmVTHH.Count <= 0)  //Không có thuốc này trong nhóm đã chọn
                    {
                        cmbNhomHang.EditValue = null;
                    }
                }
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void cmbNhomHang_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                if ((cmbNhomHang.EditValue is Guid && (Guid)cmbNhomHang.EditValue != Guid.Empty) && (cmbHang.EditValue is Guid && (Guid)cmbHang.EditValue != Guid.Empty))
                {
                    string filterExpresstion = string.Format("ID = '{0}' AND NhomVatTuHangHoaID = '{1}'", cmbHang.EditValue, cmbNhomHang.EditValue);
                    DanhMucVatTuHangHoa dmVTHH = DanhMucVatTuHangHoa.Filter(filterExpresstion);
                    if (dmVTHH.Count <= 0)  //Không có thuốc này trong nhóm đã chọn
                    {
                        cmbHang.EditValue = null;
                    }
                }
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }
    }
}
